package com.scalar.chiptrack.accountsetup.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.accountsetup.forms.AccountsSetupForm;
import com.scalar.ScalarException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * Created by IntelliJ IDEA.
 * User: Sudhakar
 * Date: Dec 18, 2007
 * Time: 4:40:24 PM
 */
public class AccountsSetupDAO
{
    /**
     * default constructor
     */
    public AccountsSetupDAO()
    {

    }

    /**
     *
     * @return
     * @throws ScalarException
     */
    public static AccountsSetupForm loadAccountsSetUpInfo() throws ScalarException
    {
        Connection conn                     = null;
        PreparedStatement pstmt             = null;
        ResultSet rset                      = null;
        AccountsSetupForm accountsSetupForm = null;
        DBTransaction dbTransaction        = null;


        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement("SELECT SO_ACCOUNTS_RECEIVABLE_ACCOUNT, SO_DISTRIBUTOR_GENERAL_ACCOUNT, CUSTOMER_GENERAL_ACCOUNT, \n" +
                    "SO_FILE_LOCATION, SO_FILE_NAME, PO_ACCOUNTS_PAYBLE_ACCOUNT, PO_GENERAL_ACCOUNT, PO_FILE_LOCATION, \n" +
                    "PO_FILE_NAME, PO_RECEIPT_FILE_LOCATION, PO_RECEIPT_FILE_NAME FROM ACCOUNTS_SETUP_TABLE");

            rset = pstmt.executeQuery();
            if (rset.next())
            {
                accountsSetupForm = new AccountsSetupForm();
                accountsSetupForm.setAccountsReceivableAccount(rset.getString("SO_ACCOUNTS_RECEIVABLE_ACCOUNT"));
                accountsSetupForm.setDistributorGeneralAccount(rset.getString("SO_DISTRIBUTOR_GENERAL_ACCOUNT"));
                accountsSetupForm.setCustomerGeneralAccount(rset.getString("CUSTOMER_GENERAL_ACCOUNT"));
                accountsSetupForm.setSoFileLoaction(rset.getString("SO_FILE_LOCATION"));
                accountsSetupForm.setSoFileName(rset.getString("SO_FILE_NAME"));
                accountsSetupForm.setAccountsPaybleAccount(rset.getString("PO_ACCOUNTS_PAYBLE_ACCOUNT"));
                accountsSetupForm.setGeneralAccount(rset.getString("PO_GENERAL_ACCOUNT"));
                accountsSetupForm.setPoFileLocation(rset.getString("PO_FILE_LOCATION"));
                accountsSetupForm.setPoFileName(rset.getString("PO_FILE_NAME"));
                accountsSetupForm.setPoReceiptFileLocation( rset.getString ( "PO_RECEIPT_FILE_LOCATION" ) );
                accountsSetupForm.setPoReceiptFileName( rset.getString ( "PO_RECEIPT_FILE_NAME" ) );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("could not retrive Accounts Setup Info", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return accountsSetupForm;
    }
    /**
     *
     * @param dbTransaction
     * @param accountsSetupForm
     * @throws ScalarException
     */
    public static void saveAccountsSetupInfo(DBTransaction dbTransaction, AccountsSetupForm accountsSetupForm) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(" { Call sp_accounts_setup_info( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            pstmt.setString(1, accountsSetupForm.getAccountsReceivableAccount());
            pstmt.setString(2, accountsSetupForm.getDistributorGeneralAccount());
            pstmt.setString(3, accountsSetupForm.getCustomerGeneralAccount());
            pstmt.setString(4, accountsSetupForm.getSoFileLoaction());
            pstmt.setString(5, accountsSetupForm.getSoFileName());
            pstmt.setString(6, accountsSetupForm.getAccountsPaybleAccount());
            pstmt.setString(7, accountsSetupForm.getGeneralAccount());
            pstmt.setString(8, accountsSetupForm.getPoFileLocation());
            pstmt.setString(9, accountsSetupForm.getPoFileName());
            pstmt.setString(10, accountsSetupForm.getPoReceiptFileLocation());
            pstmt.setString(11, accountsSetupForm.getPoReceiptFileName());

            pstmt.execute();

            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Could not save Accounts setup information ", e);
        }
        finally
        {
            if (dbTransaction == null)
            {
                if (conn != null)
                {
                    if (success == true)
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch (SQLException e)
                        {
                            e.printStackTrace();
                            throw new ScalarException("Error while committing the save Accounts setup information", e);
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch (SQLException e)
                        {
                            e.printStackTrace();
                            throw new ScalarException("Error while rolling back the save Accounts setup information", e);
                        }
                    }
                }
            }
            if (rs != null)
            {
                try
                {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
}
